import sqlite3
from sqlite3 import Error

from .singleton import singleton

from .config import DBPath


# region SQL处理语句

def get_insert_str(table_name, col_list):
    """
    获取添加数据的sql语句字符串
    :param table_name: 表名
    :param col_list: 列名列表
    :return: sql语句字符串
    """
    if col_list:
        col_str = ",".join(col_list)
        para_str = ",".join(["?"] * len(col_list))
        add_str = f'INSERT INTO {table_name} ({col_str}) values ( {para_str} )'
        return add_str


def get_update_str(table_name, col_list, from_id=0, condition_str=""):
    """
    获取更新数据语句
    """
    if len(col_list) > 0:
        set_value = []
        para_value = []

        for index, col in enumerate(col_list):
            if col == "id":
                continue
            else:
                set_value.append(f"{col}=?")
                para_value.append("?")
        para_str = ", ".join(set_value)
        if from_id:
            condition_str = f"id= {from_id}"
        sql_cmd = f"update {table_name} set {para_str} WHERE {condition_str}"
        return sql_cmd


def get_update_str_contain_id(table_name, col_list):
    return get_update_str(table_name, col_list, condition_str="id=?")


def get_find_str(table_name, condition_str=None):
    """获取查找数据语句"""
    select_str = f"select * from {table_name}"
    if condition_str:
        select_str += f" where {condition_str}"
    return select_str


# endregion

@singleton
class SQL:
    def __init__(self, path=None):
        if path:
            self.db_path = path
        else:
            self.db_path = DBPath
        self.conn = None
        self.__get_conn()

    # region 基础功能
    def __get_conn(self):
        """
        获取数据库
        :return:conn 数据库链接对象
        """
        self.conn = sqlite3.connect(self.db_path, check_same_thread=False)

    def __get_cur(self):
        """
        获取数据对象游标
        """
        if self.conn is None:
            self.__get_conn()
        return self.conn.cursor()

    # 获取数据表的列名
    def get_col_name(self, tb_name):
        """
        获取表的列名
        tb_name:数据表的名字
        返回值：list[(0, 'id', 'INTEGER', 1, None, 1)]
        """
        cur = self.__get_cur()
        cur.execute(f"PRAGMA table_info({tb_name})")
        col_list = []
        for data in cur:
            col_list.append(data[1])
        cur.close()
        return col_list

    # 处理无参数的命令
    def execute_cmd(self, cmd_str):
        cur = self.__get_cur()
        try:
            cur.execute(cmd_str)
            self.conn.commit()
        except Error as e:
            print(e)

    # 处理多数据参数命令
    def execute_many_datas(self, sql_str: str, values):
        """处理多个数据"""
        cur = self.__get_cur()
        try:
            cur.executemany(sql_str, values)
            self.conn.commit()
        except Error as e:
            print(e)

    # 处理单数据参加命令
    def execute_one_data(self, sql_str, value):
        """处理一个数据"""
        cur = self.__get_cur()
        try:
            cur.execute(sql_str, value)
            self.conn.commit()
        except Error as e:
            print(e)

    # endregion
    def insert_one_data(self, sql_str, value):
        """处理一个数据， 返回数据的id值"""
        cur = self.__get_cur()
        try:
            cur.execute(sql_str, value)
            self.conn.commit()
            return cur.lastrowid
        except Error as e:
            print(e)
            return 0

    def find_all(self, sql_cmd):
        cur = self.__get_cur()
        data = None
        try:
            data = cur.execute(sql_cmd).fetchall()
            cur.close()

        except Exception as e:
            print(e)
        return data

    def find_one(self, sql_cmd):
        cur = self.__get_cur()
        data = None
        try:
            data = cur.execute(sql_cmd).fetchone()
            cur.close()

        except Exception as e:
            print(e)
        return data

    def insert_one(self, table_name, col_list, value):
        insert_str = get_insert_str(table_name, col_list)
        return self.insert_one_data(insert_str, value)

    def insert_many(self, table_name, col_list, values):
        insert_str = get_insert_str(table_name, col_list)
        self.execute_many_datas(insert_str, values)

    def update_one(self, table_name, col_list, value, from_id=0, condition_str=""):
        update_str = get_update_str(table_name, col_list, from_id=from_id, condition_str=condition_str)
        self.execute_one_data(update_str, value)

    def update_many(self, table_name, col_list, values, id=0, condition_str=""):
        update_str = get_update_str(table_name, col_list, id=0, condition_str="")
        self.execute_many_datas(update_str, values)

    def update_many_by_id(self, table_name, col_list: list, values: list):
        """id是数据列表的第一个数据"""
        if values:
            update_str = get_update_str_contain_id(table_name, col_list)
            update_values = []
            for value in values:
                id = value[0]
                new_value = value[1:]
                new_value.append(id)
                update_values.append(new_value)
            print("更新执行的语句是:", update_str)
            self.execute_many_datas(update_str, update_values)

    def clear_table(self, table_name):
        delete_str = f"delete from {table_name}"
        self.execute_cmd(delete_str)
        set_id_str = f"update sqlite_sequence set seq=0 where name = '{table_name}'"
        self.execute_cmd(set_id_str)

    def insert_one_by_dict(self, table_name, data_dict: dict):
        if data_dict:
            keys = list(data_dict.keys())
            values = list(data_dict.values())
            col_name = self.get_col_name(table_name)
            if set(keys).issubset(set(col_name)):
                return self.insert_one(table_name, keys, values)
            else:
                print(f"字典数据中的keys:{keys}没有全部包含在数据表{table_name}列名{col_name}中")
                return 0

    def update_one_by_dict(self, table_name, data_dict: dict, data_id):
        if data_dict:
            keys = list(data_dict.keys())
            values = list(data_dict.values())
            col_name = self.get_col_name(table_name)
            if set(keys).issubset(set(col_name)):
                return self.update_one(table_name, keys, values, data_id)
            else:
                print(f"字典数据中的keys:{keys}没有全部包含在数据表{table_name}列名{col_name}中")
                return 0


class Table:
    def __init__(self, table_name):
        self.name = table_name
        self.sql = SQL()
        self.col_list = self.sql.get_col_name(self.name)
        self.insert_col_list = self.col_list[1:-1]
        self.update_col_list = []
        self.key_col = "id"

    def insert_row(self, values, col_list=None):
        if not col_list:
            col_list = self.insert_col_list

        insert_str = get_insert_str(self.name, col_list)
        return self.sql.insert_one(insert_str, values)

    def insert_rows(self, values, col_list=None):
        if not col_list:
            col_list = self.insert_col_list

        insert_str = get_insert_str(self.name, col_list)
        self.sql.execute_many_datas(insert_str, values)

    def update_row(self, values, condition, col_list=None):
        if not col_list:
            col_list = self.update_col_list
        update_str = get_update_str(self.name, col_list, condition_str=condition)

        self.sql.execute_one_data(update_str, tuple(values))

    def update_one_col(self, rol_name, value, condition):
        sql_str = f"update {self.name} set {rol_name} = ? where {condition}"
        self.sql.execute_one_data(sql_str, (value,))

    def find_rows(self, condition_str):
        """查照符合所有条件的行"""
        find_str = get_find_str(self.name, condition_str)
        return self.sql.find_all(find_str)

    def find_one(self, condition_str):
        """查找一行"""
        find_str = get_find_str(self.name, condition_str)
        return self.sql.find_one(find_str)

    def clear_data(self):

        self.sql.clear_table(self.name)


# region 通用方法
def update_one_col(table_name,col_name,value,condition_str):
    sql_str = f"update {table_name} set {col_name}=? where {condition_str}"
    sql = SQL()
    sql.execute_one_data(sql_str,value)